IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spCDSNCreateNewUser')
	BEGIN
		PRINT 'Dropping Procedure spCDSNCreateNewUser'
		DROP  Procedure  spCDSNCreateNewUser    
	END

GO

PRINT 'Creating Procedure spCDSNCreateNewUser'
GO
/******************************************************************************
**		VSS File: $/SPAddNewUser.sql 
**		Name: spCDSNCreateNewUser    
**		Desc: 	This procedure adds a new user from Active Directory into the CDS.
**              
**		Return values: None
** 
**		Called by:   
**              AD_To_CDS.dtsx
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE Procedure spCDSNCreateNewUser (
	/* Param List */
	@username	varchar(36),
	@firstName	varchar(50),
	@lastName	varchar(50),
	@countryCode	int = 0,
	@primOrgId		int = 1,
	@email		varchar(100) = NULL,
	@areaCode	varchar(3) = NULL,
	@telephoneNumber varchar(20) = NULL, 
	@defPrincipalTypeId int = 1,
	@defOrgId		int = 1,
	@defFacilityId	int = 1,
	@defEmailTypeId int = 1,
	@defPhoneTypeId int = 1
)	   


AS    
    ------------------------
    -- CONSTANTS
    DECLARE @DEFAULT_APP_BITMASK  AS INT
    SET @DEFAULT_APP_BITMASK = 0
    
    ------------------------
    
    -- Placeholders for IDs passed back and 
	-- current max IDs of tables to be inserted
	-- into, since there are no Identities
    DECLARE
	        @_errorCode					AS INT,
			@_maxPrincipalId			AS INT,
			@_maxAdmPersonId			AS INT,
			@_maxAdmInternalPersonId	AS INT,
			@_maxFwkDomainUserId		AS INT,
			@_emailDescription			AS VARCHAR(128)

	SET @_errorCode  = 0
	SET @_emailDescription = 'Primary Email'

	-- Setup the IDs to be inserted by selecting the max
	-- Id of the column, and if null, start at 1.
	SET @_maxPrincipalId = COALESCE (((SELECT MAX(Id) from FwkDomainPrincipal)+1), 1)

	-- AdmPerson
	SET @_maxAdmPersonId = COALESCE (((SELECT MAX(Id) from AdmPerson)+1), 1)

	-- AdmInternalPerson	
	SET @_maxAdmInternalPersonId = COALESCE (((SELECT MAX(Id) from AdmInternalPerson)+1), 1)

	--	FwkDomainUser
	SET @_maxFwkDomainUserId = COALESCE (((SELECT MAX(Id) from FwkDomainUser)+1), 1)


	-- START THE TRANS!	    
        BEGIN TRANSACTION spCDSNCreateNewUser 
           
        -- DmnPrincipal Insert 
	    INSERT INTO FwkDomainPrincipal ( Id,	
									FwkDomainPrincipalTypeID, 
									IsActive )
				VALUES 
							(	@_maxPrincipalId,
								@defPrincipalTypeId, 
								1 )           
								
        SET @_errorCode = @@ERROR
        
        IF @_errorCode <> 0 BEGIN
            ROLLBACK TRANSACTION spCDSNCreateNewUser
            RAISERROR('Unable to create FwkDomainPrincipal record for user %s', 16, 1, @userName)
            RETURN @_errorCode
        END
        
        ELSE BEGIN
            -- Check the Organization ID passed in isn't null, if it is, use the default.
            IF @primOrgId IS NULL 
				SET @primOrgId = @defOrgId
            END

            -- DmnUser Insert.
            INSERT INTO FwkDomainUser( Id, 
									username, 
									FwkPrincipalID, 
									FwkApplicationBitmask)
									
                VALUES (			@_maxFwkDomainUserId,
									@userName, 
									@_maxPrincipalId,	
									@DEFAULT_APP_BITMASK)
            SET @_errorCode = @@ERROR
    
            IF @_errorCode <> 0 BEGIN
                ROLLBACK TRANSACTION spCDSNCreateNewUser
				RAISERROR('Unable to create FwkDomainUser record for user %s', 16, 1, @userName)                    
				RETURN @_errorCode
            END  
				
            
            -- DmnPerson Insert
            INSERT INTO AdmPerson (	Id,
									FirstName, 
									LastName, 
									Country,
									AdmPersonTypeId,
									FwkDomainUserId)
									
                VALUES			(	@_maxAdmPersonId,
									@firstName, 
									@lastName, 
									@countryCode, 
									1,
									@_maxFwkDomainUserId )
									
            SET @_errorCode = @@ERROR
        
            IF @_errorCode <> 0 BEGIN
                ROLLBACK TRANSACTION spCDSNCreateNewUser
                RAISERROR('Unable to create AdmPerson record for user %s', 16, 1, @userName)
                RETURN @_errorCode
            END
            ELSE BEGIN
				-- AdmInternalPerson Insert.
				INSERT INTO AdmInternalPerson ( Id,
												AdmPersonId,
												PrimaryFacilityId,
												PrimaryOrganizationId ) 
												VALUES
											  ( @_maxAdmInternalPersonId,
												@_maxAdmPersonId,
												@defFacilityId,
												@primOrgId )

                IF @_errorCode <> 0 BEGIN
					ROLLBACK TRANSACTION spCDSNCreateNewUser
					RAISERROR('Unable to create AdmInternalPerson record for user %s', 16, 1, @userName)
					RETURN @_errorCode
				END
                -- Email Insert
                ELSE BEGIN
                
					-- Check if there's an email to insert first.
					IF @email IS NOT NULL AND @email != '' BEGIN
						EXEC spCdsNCreateAdmEmailAddress	@email, 
															@_maxAdmPersonId, 
															@defEmailTypeId, 
															@_emailDescription  
					END
					
					-- PHONE insert- only if there is a phone no. to insert.
					IF @telephoneNumber != '' AND @areaCode IS NOT NULL AND @telephoneNumber IS NOT NULL BEGIN
					
						EXEC spCdsNCreateAdmTelephoneNumber @countryCode,
															@areaCode, 
															@telephoneNumber, 
															@_maxAdmPersonId, 
															@defPhoneTypeId 
					END
			
				COMMIT TRANSACTION spCDSNCreateNewUser
       END
    END


GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
